from sys import path as path2
from os import path
from time import sleep, localtime, strftime, time
import random, json
from threading import Thread, active_count

import requests
from bs4 import BeautifulSoup
from rich import print as rprint
from py3langid.langid import LanguageIdentifier, MODEL_FILE

path2.append('c:/users/tian/desktop')
from Service import service, ua, ips



# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 临时区

# result = service.query2('select count(*) from guochan where num not in (select min(num) from guochan group by title) and tag=" "')[0][0]
# rprint(result)


service.path = 'd:/data/numdomain.db'


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 将数据转移到新文件中，填充字段信息


results = service.query2('select tag, title, url, link, magnet, size, duration, postdate, recorddate, site, status from western')
service.path = 'd:/data/total.db'
service.exec2('insert into western (tag, title, url, link, magnet, size, duration, postdate, recorddate, site, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', results)
# service.exec('update guochan set size="unknown" where size is null')
# service.exec('update guochan set duration="unknown" where duration is null')
# service.exec('update guochan set site="GCBT" where site is null')



# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 删除 title 中的空格和非正常的空格


# service.exec('update guochan set title=replace(title, " ", "") where title like "% %"')
# service.exec('update guochan set title=replace(title, " ", "") where title like "% %"')


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 删除 title 重复的资源


# original = service.query('select count(*) from guochan')[0][0]
# # 查询
# results = service.query2('select count(*) from guochan where num not in (select min(num) from guochan group by title) and tag="必看"')
# rprint('重复的数量：', results)
# # 删除
# # service.exec('delete from guochan where num not in (select min(num) from guochan group by magnet)')

# now  = service.query('select count(*) from guochan')[0][0]
# num = original - now
# rprint('删除重复的数量：', num)
# service.exec('vacuum')        # 清除所有的 vacuum 数据


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 根据 list名单 修改，包括删除含过滤词的资源、修改与现有标准不符的 tag

# identifier = LanguageIdentifier.from_pickled_model(MODEL_FILE, norm_probs=True)
# identifier.set_languages(['zh', 'en', 'ja'])


# def aboutTitle(title):
#     with open('d:/data/list.json', 'r', encoding='utf-8') as f:
#         keywords = json.loads(f.read())
#     whitelist = keywords['whitelist']
#     blacklist = keywords['blacklist']
#     secondary = keywords['secondary']
#     anchorman = keywords['anchorman']
#     toupai = keywords['toupai']
#     others = keywords['others']
#     for k in whitelist:
#         if k in title:
#             tag = '必看'
#             return tag
#     lang = identifier.classify(title)[0]
#     if lang == 'ja':
#         service.exec('delete from guochan where title=?', (title,))
#         print('日语，已删除')
#         return
#     elif lang == 'en':
#         return ' '
#     for k in blacklist:
#         if k in title:
#             service.exec('delete from guochan where title=?', (title,))
#             print(f'含过滤词 {k}，已删除')
#             return
#     for k in anchorman:
#         if k in title:
#             tag = '主播'
#             return tag
#     for k in toupai:
#         if k in title:
#             tag = '偷拍'
#             return tag
#     for k in others:
#         if k in title:
#             tag = k
#             return tag
#     for k in secondary:
#         if k in title:
#             tag = '探索'
#             return tag
#     tag = ' '
#     return tag

# service.path = 'd:/data/sukebei.db'
# results = service.query('select num, tag, title from guochan')
# original = len(results)
# for result in results:
#     num, tag, title = result
#     ctag = aboutTitle(title)
#     if ctag == None or ctag == tag:
#         continue
#     else:
#         service.exec('update guochan set tag=? where num=?', (ctag, num))
#         rprint(f'修改：{title}，将 {tag} 改为 {ctag}')
# now = service.query2('select count(*) from guochan')[0][0]
# num = original - now
# rprint('删除的数量：', num)
# service.exec('vacuum')        # 清除所有的 vacuum 数据

# files = ['caoliu.db', 'gcbt.db', 'tanhuazu.db', 'taosebt.db']
# for file in files:
#     service.path = path.join('d:/data', file)
#     results = service.query('select num, tag, title from guochan')
#     original = len(results)
#     for result in results:
#         num, tag, title = result
#         ctag = aboutTitle(title)
#         if ctag == None or ctag == tag:
#             continue
#         else:
#             service.exec('update guochan set tag=? where num=?', (ctag, num))
#             rprint(f'修改：{title}，将 {tag} 改为 {ctag}')
#     now = service.query2('select count(*) from guochan')[0][0]
#     num = original - now
#     rprint(file, '删除的数量：', num)
#     service.exec('vacuum')        # 清除所有的 vacuum 数据
#     sleep(5)


# --------------------------------------------------------------------------------------------------------------------------------------------------------
# 重写 num
# 问题没有解决，


# results = service.query('select rowid, num from guochan')
# rprint(results[-5:])
# service.exec('vacuum')
# i = 0
# for result in results:
#     i += 1
#     rowid, num = result
#     if rowid == num:
#         continue
#     service.exec('update guochan set num=? where rowid=?', (rowid, rowid))
#     if i % 100 == 0:
#         print(i)
